{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "048105ee",
   "metadata": {},
   "source": [
    "### Best Practices for Data Loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30afd415",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import modules\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3f9beb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# demo data\n",
    "laundry_mat_data = [\n",
    "    {\"product\": \"Detergent\", \"dollar_price_per_unit\": 4.5, \"quantity\": 100, \"total_cost\": 200},\n",
    "    {\"product\": \"Dryer Sheets Box\", \"dollar_price_per_unit\": 3.5, \"quantity\": 100, \"total_cost\": 350},\n",
    "    {\"product\": \"Washing Machine\", \"dollar_price_per_unit\": 400, \"quantity\": 25, \"total_cost\": 10000},\n",
    "    {\"product\": \"Dryer\", \"dollar_price_per_unit\": 400, \"quantity\": 25, \"total_cost\": 10000},\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52844b6f",
   "metadata": {},
   "source": [
    "Full Data Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce9ea91d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def perform_full_data_load(laundry_mat_data):\n",
    "    conn = sqlite3.connect(\"laundry_mat.db\")  # Connect to the database\n",
    "    cursor = conn.cursor()\n",
    "\n",
    "    # Truncate the existing data\n",
    "    cursor.execute(\"DELETE FROM laundry_mat\")\n",
    "\n",
    "    # Insert new data\n",
    "    for record in laundry_mat_data:\n",
    "        cursor.execute(\"INSERT INTO laundry_mat (product, dollar_price_per_unit, quantity, total_cost) VALUES (?, ?, ?, ?)\",\n",
    "                       (record[\"product\"], record[\"dollar_price_per_unit\"], record[\"quantity\"], record[\"total_cost\"]))\n",
    "\n",
    "    conn.commit()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7eefd75b",
   "metadata": {},
   "outputs": [],
   "source": [
    "perform_full_data_load(laundry_mat_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40157e58",
   "metadata": {},
   "source": [
    "Incremental Data Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "353fbcd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def perform_incremental_data_load(laundry_mat_data):\n",
    "    conn = sqlite3.connect(\"laundry_mat.db\")  # Connect to the database\n",
    "    cursor = conn.cursor()\n",
    "\n",
    "    # Insert new data (if the product doesn't already exist)\n",
    "    for record in laundry_mat_data:\n",
    "        cursor.execute(\"INSERT OR IGNORE INTO laundry_mat (product, dollar_price_per_unit, quantity, total_cost) VALUES (?, ?, ?, ?)\",\n",
    "                       (record[\"product\"], record[\"dollar_price_per_unit\"], record[\"quantity\"], record[\"total_cost\"]))\n",
    "\n",
    "    conn.commit()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37075fd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "perform_incremental_data_load(laundry_mat_data)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
